﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Skyworth.CRM.Service.Entity;
using Skyworth.CRM.Service.IDAL;

namespace Skyworth.CRM.DAL
{
   public class PricePolicyDAL:SuperDataBase,IPricePolicyDAL
    {
       public void Add(PricePolicy model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("insert into T_Sales_PricePolicy(");
           strSql.Append(" PolicyName,PolicyCode)");
           strSql.Append(" values ( @Name,@Code)");
               
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Name", DbType.String, model.PolicyName);
           DB.AddInParameter(cmd, "@Code", DbType.String, model.PolicyCode);
           DB.ExecuteNonQuery(cmd);
       }

       public void Update(PricePolicy model)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append("update T_Sales_PricePolicy set ");
           strSql.Append("PolicyName=@Name,");
           strSql.Append("PolicyCode=@Code");
           strSql.Append(" where PolicyId=@Id");
           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@ID", DbType.Int32, model.PolicyId);
           DB.AddInParameter(cmd, "@Name", DbType.String, model.PolicyName);
           DB.AddInParameter(cmd, "@Code", DbType.String, model.PolicyCode);
           DB.ExecuteNonQuery(cmd);
       }

       public DataTable Query(string strWhere, Hashtable parameterValue)
       {
           string strCommandText = "SELECT PolicyId,PolicyName,PolicyCode FROM [T_Sales_PricePolicy] ";
           if (strWhere != "")
           {
               strCommandText = strCommandText + strWhere;
           }
           System.Data.Common.DbCommand sqlcommand = DB.GetSqlStringCommand(strCommandText);
           if (parameterValue != null && parameterValue.Count != 0)
           {
               foreach (DictionaryEntry de in parameterValue)
               {
                   DB.AddInParameter(sqlcommand, de.Key.ToString(), DbType.String);
                   DB.SetParameterValue(sqlcommand, de.Key.ToString(), de.Value.ToString());
               }
           }
           DataSet ds = DB.ExecuteDataSet(sqlcommand);
           return ds.Tables[0];
       }

       public void Delete(int policyId)
       {
           StringBuilder strSql = new StringBuilder();
           strSql.Append(" begin transaction ");
           strSql.Append(" delete T_Sales_PricePolicy where PolicyId=@Id");
           strSql.Append(" delete T_Sales_PriceCust where PolicyId=@Id");
           strSql.Append(" delete T_Sales_PriceList where PolicyId=@Id");
           strSql.Append(" IF (@@ERROR>0) ROLLBACK ELSE COMMIT ");

           DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
           DB.AddInParameter(cmd, "@Id", DbType.Int32, policyId);
           DB.ExecuteNonQuery(cmd);
       }

       public PricePolicy GetPolicyById(int policyId)
       {
            PricePolicy model = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  top 1 PolicyId,PolicyName,PolicyCode from T_Sales_PricePolicy ");
                strSql.Append(" where PolicyId=@PolicyId");
                DbCommand cmd = DB.GetSqlStringCommand(strSql.ToString());
                DB.AddInParameter(cmd, "@PolicyId", DbType.Int32, policyId);
                using (IDataReader dr = DB.ExecuteReader(cmd))
                {
                    while (dr.Read())
                    {
                        model = new PricePolicy();
                        if (dr["PolicyId"].ToString() != "")
                        {
                            model.PolicyId = int.Parse(dr["PolicyId"].ToString());
                        }
                        model.PolicyName = dr["PolicyName"].ToString();
                        model.PolicyCode = dr["PolicyCode"].ToString();
                    }
                }
           return model;
       }

       public DataTable GetPoliciesByPage(string strWhere, string strOrderBy, int pageIndex, int pageSize, out int recordCount, out int pageCount)
       {
           recordCount = 0;
           pageCount = 0;
           StringBuilder strCommandText = new StringBuilder();
           DataSet ds;
           strCommandText.AppendFormat(
                  "SELECT row_number() over({0}) AS rownum, PolicyId, PolicyName, PolicyCode from T_Sales_PricePolicy", strOrderBy);
           strCommandText.Append(strWhere);

           System.Data.Common.DbCommand sqlcommand = DB.GetStoredProcCommand("sp_GetPager");
           DB.AddInParameter(sqlcommand, "@Psql", DbType.String, strCommandText.ToString());
           DB.AddInParameter(sqlcommand, "@PNum", DbType.Int32, pageIndex);
           DB.AddInParameter(sqlcommand, "@PSize", DbType.Int32, pageSize);
           DB.AddInParameter(sqlcommand, "@orderBy", DbType.String, "");
           DB.AddInParameter(sqlcommand, "@RowNumName", DbType.String, "rownum");
           DB.AddOutParameter(sqlcommand, "@pTotalRecords", DbType.Int32, 4);
           DB.AddOutParameter(sqlcommand, "@pTotalPages", DbType.Int32, 4);
           ds = DB.ExecuteDataSet(sqlcommand);
           recordCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalRecords"));
           pageCount = Convert.ToInt32(DB.GetParameterValue(sqlcommand, "@pTotalPages"));

           return ds.Tables[0];
       }
    }
}
